﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using NPOI.HSSF.UserModel;
using System.IO;
using NPOI.SS.UserModel;
using System.Data;
using Microsoft.SharePoint;
using OfficeOpenXml;

namespace UE.SharePoint.ImportExport.BLL.Excel
{
    /// <summary>
    /// This is used to read excel data
    /// </summary>
    public class XLSXReader : IReader
    {
        private Setting _setting;
        private ExcelWorksheet _workSheet;
        private ExcelWorkbook _workBook;

        /// <summary>
        /// ctor
        /// </summary>
        public XLSXReader(Setting setting)
        { 
            Setting = setting;
        }

        /// <summary>
        /// gets or sets the setting of the context
        /// </summary>
        public Setting Setting
        {
            get { return _setting; }
            set { _setting = value; }
        }

        /// <summary>
        /// 
        /// </summary>
        public ExcelWorkbook WorkBook
        {
            get
            {
                if (null == _workSheet)
                {
                    if (Setting.IsSPFile)
                    {
                        SPFolder spFolder = Setting.SPWeb.GetFolder(Setting.SPFolderUrl);
                        Stream stream = spFolder.Files[Setting.FileName].OpenBinaryStream();
                        ExcelPackage excelPackage = new ExcelPackage(stream);
                        _workBook = excelPackage.Workbook;
                        stream.Close();
                    }
                    else
                    {
                        using (FileStream stream = new FileStream(Setting.FileName, FileMode.Open, FileAccess.Read))
                        {
                            ExcelPackage excelPackage = new ExcelPackage(stream);
                            _workBook = excelPackage.Workbook;
                        }
                    }
                }

                return _workBook;
            }
        }

        /// <summary>
        /// gets the current excel book
        /// </summary>
        public ExcelWorksheet WorkSheet
        {
            get
            {
                return WorkBook.Worksheets[1];
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public DataTable GetData()
        {
            DataTable result = new DataTable();
            int headerRowId = 1; 

            //set columns
            for (int i = 1; i <= WorkSheet.Dimension.End.Column; i++)
            {
                result.Columns.Add(WorkSheet.GetValue(headerRowId,i).ToString(), typeof(String));
            }

            //set data
            for (int i = headerRowId + Setting.StartRow; i <= WorkSheet.Dimension.End.Row; i++)
            {
                DataRow targetRow = result.NewRow();
                for (int j = 1; j <= WorkSheet.Dimension.End.Column; j++)
                {
                    targetRow[j-1] = WorkSheet.GetValue(i, j).ToString();
                }

                result.Rows.Add(targetRow);
            }

            return result;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="cell"></param>
        /// <returns></returns>
        private string GetValue(Cell cell)
        {
            switch (cell.CellType)
            {
                case CellType.NUMERIC:
                    return cell.NumericCellValue.ToString();
                default:
                    return cell.StringCellValue;
            }
        } 

        /// <summary>
        /// 
        /// </summary>
        /// <param name="setting"></param>
        /// <returns></returns>
        public Column[] LoadColumns()
        {
            int rowId = 1;
            int cellCount = WorkSheet.Dimension.End.Column;
            Column[] columns = new Column[cellCount];

            for (int i = 1; i <= cellCount; i++)
            {
                columns[i] = new Column(WorkSheet.GetValue(rowId, i).ToString());
            }

            return columns;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="setting"></param>
        /// <returns></returns>
        public string[] LoadTables()
        {
            string[] tables = new string[WorkBook.Worksheets.Count];
            for (int i = 0; i < WorkBook.Worksheets.Count; i++)
            {
                tables[i] = WorkBook.Worksheets[i].Name;
            }

            return tables;
        }
    }
}
